from fastapi import APIRouter, Depends, HTTPException, status, Query, Path
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.sql import select
from typing import List, Optional, Dict, Any
from datetime import datetime
import json

from app.models.db import get_async_session
from app.models.db import DataSource, DataSet, DataField, FieldChangeLog
from app.services.dataset_service import get_field_relationship

router = APIRouter(
    prefix="/metadata",
    tags=["元数据服务"]
)


@router.get("/datasets", response_model=Dict[str, Any])
async def get_datasets_metadata(
    session: AsyncSession = Depends(get_async_session)
):
    """获取所有数据集的元数据，用于大模型理解数据结构
    
    Returns:
        数据集元数据列表
    """
    try:
        # 获取所有数据集
        datasets_result = await session.execute(
            select(DataSet).order_by(DataSet.name)
        )
        datasets = datasets_result.scalars().all()
        
        # 构建元数据响应
        metadata = []
        
        for dataset in datasets:
            # 获取数据源信息
            datasource_result = await session.execute(
                select(DataSource).filter(DataSource.id == dataset.data_source_id)
            )
            datasource = datasource_result.scalar_one_or_none()
            
            # 获取字段信息
            fields_result = await session.execute(
                select(DataField).filter(
                    DataField.dataset_id == dataset.id
                ).order_by(DataField.ordinal_position)
            )
            fields = fields_result.scalars().all()
            
            # 构建数据集元数据
            dataset_metadata = {
                "id": dataset.id,
                "name": dataset.name,
                "description": dataset.description,
                "type": dataset.type,
                "category": dataset.category,
                "tags": dataset.get_tags_list(),
                "data_source": {
                    "id": datasource.id,
                    "name": datasource.name,
                    "type": datasource.type
                } if datasource else None,
                "fields": []
            }
            
            # 添加字段元数据
            for field in fields:
                field_metadata = {
                    "id": field.id,
                    "name": field.name,
                    "display_name": field.display_name or field.name,
                    "description": field.description,
                    "data_type": field.data_type,
                    "is_primary_key": field.is_primary_key,
                    "is_nullable": field.is_nullable,
                    "synonyms": field.get_synonyms_list(),
                    "business_terms": field.get_business_terms(),
                    "sample_values": field.get_sample_values(),
                    "semantic_type": field.semantic_type,
                    "statistics": {
                        "min_value": field.min_value,
                        "max_value": field.max_value,
                        "avg_value": field.avg_value,
                        "distinct_count": field.distinct_count,
                        "null_count": field.null_count,
                        "null_percentage": field.null_percentage
                    }
                }
                
                dataset_metadata["fields"].append(field_metadata)
            
            metadata.append(dataset_metadata)
        
        return {
            "success": True,
            "data": metadata
        }
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取数据集元数据失败: {str(e)}"
        )


@router.get("/datasets/{dataset_id}", response_model=Dict[str, Any])
async def get_dataset_metadata(
    dataset_id: str = Path(..., title="数据集ID"),
    session: AsyncSession = Depends(get_async_session)
):
    """获取指定数据集的元数据，用于大模型理解数据结构
    
    Args:
        dataset_id: 数据集ID
        
    Returns:
        数据集元数据
    """
    try:
        # 获取数据集
        dataset_result = await session.execute(
            select(DataSet).filter(DataSet.id == dataset_id)
        )
        dataset = dataset_result.scalar_one_or_none()
        
        if not dataset:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"数据集不存在: {dataset_id}"
            )
        
        # 获取数据源信息
        datasource_result = await session.execute(
            select(DataSource).filter(DataSource.id == dataset.data_source_id)
        )
        datasource = datasource_result.scalar_one_or_none()
        
        # 获取字段信息
        fields_result = await session.execute(
            select(DataField).filter(
                DataField.dataset_id == dataset.id
            ).order_by(DataField.ordinal_position)
        )
        fields = fields_result.scalars().all()
        
        # 获取关系信息
        relationships = await get_field_relationship(dataset_id, session)
        
        # 构建数据集元数据
        dataset_metadata = {
            "id": dataset.id,
            "name": dataset.name,
            "description": dataset.description,
            "type": dataset.type,
            "category": dataset.category,
            "tags": dataset.get_tags_list(),
            "data_source": {
                "id": datasource.id,
                "name": datasource.name,
                "type": datasource.type
            } if datasource else None,
            "fields": [],
            "relationships": relationships
        }
        
        # 添加字段元数据
        for field in fields:
            field_metadata = {
                "id": field.id,
                "name": field.name,
                "display_name": field.display_name or field.name,
                "description": field.description,
                "data_type": field.data_type,
                "is_primary_key": field.is_primary_key,
                "is_nullable": field.is_nullable,
                "synonyms": field.get_synonyms_list(),
                "business_terms": field.get_business_terms(),
                "sample_values": field.get_sample_values(),
                "semantic_type": field.semantic_type,
                "statistics": {
                    "min_value": field.min_value,
                    "max_value": field.max_value,
                    "avg_value": field.avg_value,
                    "distinct_count": field.distinct_count,
                    "null_count": field.null_count,
                    "null_percentage": field.null_percentage
                }
            }
            
            dataset_metadata["fields"].append(field_metadata)
        
        return {
            "success": True,
            "data": dataset_metadata
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取数据集元数据失败: {str(e)}"
        )


@router.get("/search", response_model=Dict[str, Any])
async def search_metadata(
    term: str = Query(..., description="搜索词，可以是字段名、同义词或业务术语"),
    session: AsyncSession = Depends(get_async_session)
):
    """通过搜索词查找相关字段和数据集
    
    Args:
        term: 搜索词
        
    Returns:
        匹配的字段和数据集列表
    """
    try:
        # 获取所有字段
        fields_result = await session.execute(
            select(DataField).order_by(DataField.name)
        )
        fields = fields_result.scalars().all()
        
        # 匹配结果
        matched_fields = []
        
        for field in fields:
            score = 0
            match_type = None
            
            # 匹配字段名
            if term.lower() in field.name.lower():
                score += 10
                match_type = "field_name"
            
            # 匹配显示名称
            if field.display_name and term.lower() in field.display_name.lower():
                score += 8
                match_type = match_type or "display_name"
            
            # 匹配描述
            if field.description and term.lower() in field.description.lower():
                score += 5
                match_type = match_type or "description"
            
            # 匹配同义词
            synonyms = field.get_synonyms_list()
            for synonym in synonyms:
                if term.lower() in synonym.lower():
                    score += 9
                    match_type = match_type or "synonym"
                    break
            
            # 匹配业务术语
            business_terms = field.get_business_terms()
            for term_key, term_value in business_terms.items():
                if term.lower() in term_key.lower() or (term_value and term.lower() in term_value.lower()):
                    score += 9
                    match_type = match_type or "business_term"
                    break
            
            # 如果有匹配，添加到结果
            if score > 0:
                # 获取数据集信息
                dataset_result = await session.execute(
                    select(DataSet).filter(DataSet.id == field.dataset_id)
                )
                dataset = dataset_result.scalar_one_or_none()
                
                if dataset:
                    matched_fields.append({
                        "field": {
                            "id": field.id,
                            "name": field.name,
                            "display_name": field.display_name or field.name,
                            "data_type": field.data_type,
                            "description": field.description
                        },
                        "dataset": {
                            "id": dataset.id,
                            "name": dataset.name
                        },
                        "score": score,
                        "match_type": match_type
                    })
        
        # 按相关性排序
        matched_fields.sort(key=lambda x: x["score"], reverse=True)
        
        return {
            "success": True,
            "data": {
                "term": term,
                "matches": matched_fields
            }
        }
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"搜索元数据失败: {str(e)}"
        )


@router.get("/fields/{field_id}", response_model=Dict[str, Any])
async def get_field_metadata(
    field_id: str = Path(..., title="字段ID"),
    session: AsyncSession = Depends(get_async_session)
):
    """获取指定字段的详细元数据
    
    Args:
        field_id: 字段ID
        
    Returns:
        字段元数据
    """
    try:
        # 获取字段
        field_result = await session.execute(
            select(DataField).filter(DataField.id == field_id)
        )
        field = field_result.scalar_one_or_none()
        
        if not field:
            raise HTTPException(
                status_code=status.HTTP_404_NOT_FOUND,
                detail=f"字段不存在: {field_id}"
            )
        
        # 获取数据集信息
        dataset_result = await session.execute(
            select(DataSet).filter(DataSet.id == field.dataset_id)
        )
        dataset = dataset_result.scalar_one_or_none()
        
        # 构建字段元数据
        field_metadata = {
            "id": field.id,
            "name": field.name,
            "display_name": field.display_name or field.name,
            "description": field.description,
            "data_type": field.data_type,
            "is_primary_key": field.is_primary_key,
            "is_nullable": field.is_nullable,
            "synonyms": field.get_synonyms_list(),
            "business_terms": field.get_business_terms(),
            "sample_values": field.get_sample_values(),
            "semantic_type": field.semantic_type,
            "statistics": {
                "min_value": field.min_value,
                "max_value": field.max_value,
                "avg_value": field.avg_value,
                "distinct_count": field.distinct_count,
                "null_count": field.null_count,
                "null_percentage": field.null_percentage
            },
            "dataset": {
                "id": dataset.id,
                "name": dataset.name,
                "description": dataset.description
            } if dataset else None
        }
        
        return {
            "success": True,
            "data": field_metadata
        }
    
    except HTTPException:
        raise
    
    except Exception as e:
        raise HTTPException(
            status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
            detail=f"获取字段元数据失败: {str(e)}"
        ) 